package db;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;

import db.type.Column;
import db.type.Index;
import db.type.Reference;
import db.type.Schema;
import db.type.Table;

/**
 * xxx
 */
public class Rantanplan {
	
	/** logger **/
	private static org.apache.log4j.Logger log = Logger.getLogger(Rantanplan.class);
	
	/** native statments **/
	private AbstractDbNativeStatements dbStatements = null;
	
	/** connection **/
	private Connection connection = null;
	
	/** context **/
	private RantanplanContext context = null;

	/**
	 * Constructor
	 * 
	 * @param context
	 */
	public Rantanplan(RantanplanContext context) {
		this.context = context;
		
		if (this.context.getDriverClass() == null) {
			log.error("no driver class specified: " + this.context.getDriverClass());
			System.exit(-1);
		}
		
		
		if (this.context.getDriverClass().indexOf("mysql") >= 0) {
			dbStatements = new MySqlNativeStatements();
		}
		if (this.context.getDriverClass().indexOf("oracle") >= 0) {
			dbStatements = new OracleNativeStatements();
		}
		if (this.context.getDriverClass().indexOf("db2") >= 0) {
			dbStatements = new Db2NativeStatements();
		}

		if (dbStatements == null) {
			log.error("no databasetyp identified by driver class: " + this.context.getDriverClass());
			System.exit(-1);
		}		
		
		DataSource dataSource = 
			setupDataSource(
					this.context.getConnectionUrl(),
					this.context.getSchema(), 
					this.context.getUser(), 
					this.context.getPassword());
		log.debug("setup dataSource");

		try {
			connection = dataSource.getConnection();			
		} catch (SQLException e) {
			log.error("", e);
		}
	}

	/**
	 * Setup the Data-Source.
	 * 
	 * @param connectionString
	 * @param schema
	 * @param user
	 * @param password
	 * @return
	 */
	private DataSource setupDataSource(String connectionString, String schema, String user, String password) {
		BasicDataSource ds = new BasicDataSource();
		ds.setDriverClassName(this.context.getDriverClass());
		
		if (dbStatements instanceof MySqlNativeStatements) {
			StringBuffer connectionUri = new StringBuffer();
			connectionUri.append("jdbc:mysql://localhost/");
			connectionUri.append(schema);
			connectionUri.append("?").append("user=").append(user);
			if (password != null) {
				connectionUri.append("&password=").append(password);
			}
			ds.setUrl(connectionUri.toString());
		}
		
		if (dbStatements instanceof OracleNativeStatements) {
			ds.setUsername(user);
			ds.setUrl(connectionString);
			if (password != null) {
				ds.setPassword(password);
			}
		}
		
		if (dbStatements instanceof Db2NativeStatements) {
			ds.setUsername(user);
			ds.setUrl(connectionString);
			if (password != null) {
				ds.setPassword(password);
			}
		}
		
		return ds;
	}

	/**
	 * Returns all tables for the context.
	 * @return
	 */
	public ArrayList<Table> getAllTables() {
		ArrayList<Table> result = new ArrayList<Table>();
		Schema schema = new Schema(this.context.getSchema());

		ResultSet rset = null;
		Statement stmt = null;
		String sql = null;
		try {
			stmt = connection.createStatement();
			log.debug("execute select table query");
			
			sql= dbStatements.getAllTablesSql(this.context.getSchema());
			log.debug("read all tables sql: " + sql);
			rset = stmt.executeQuery(sql);

			while (rset.next()) {
				result.add(new Table(schema, rset.getString("table_name")));
			}
		} catch (SQLException e) {
			log.error("", e);
		} finally {
			try {
				rset.close();
			} catch (Exception e) {
			}
			try {
				stmt.close();
			} catch (Exception e) {
			}
		}

		return result;
	}
	

	/**
	 * Returns all columns for the table.
	 * @param table
	 * @return
	 */
	public ArrayList<Column> getAllColumns(String tableName) {
		ArrayList<Column> result = new ArrayList<Column>();
		ArrayList<String> pkColumns = getPrimaryKeyColumns(tableName);
		Schema schema = new Schema(this.context.getSchema());
		Table table = new Table(schema, tableName);

		ResultSet rset = null;
		Statement stmt = null;
		String sql = null;
		try {
			log.debug("execute select column query");
			stmt = connection.createStatement();
			sql= dbStatements.getAllColumnsSql(this.context.getSchema(), tableName);
			
			log.debug("read all columns sql: " + sql);
			rset = stmt
					.executeQuery(sql);

			while (rset.next()) {
				Column col = new Column(table, rset.getString("column_name"));
				col.setType(rset.getString("data_type"));
				col.setLength(rset.getLong("data_length"));
				col.setNullable(rset.getString("nullable").equals("Y"));
				String comment = rset.getString("column_comment");
				if (comment != null) {
					col.setComment(comment);
				}
				
				if (pkColumns == null
						|| pkColumns.size() == 0
						|| pkColumns.contains(col.getName()) ) {
					col.setPk(true);
				}
				result.add(col);
			}
		} catch (SQLException e) {
			log.error("", e);
		} finally {
			try {
				rset.close();
			} catch (Exception e) {
			}
			try {
				stmt.close();
			} catch (Exception e) {
			}
		}

		return result;		
	}
	
	/**
	 * Returns all references (fk contraints) for the
	 * given table.
	 * @param table
	 * @return
	 */
	public ArrayList<Reference> getAllReferences(String table) {
		ArrayList<Reference> result = new ArrayList<Reference>();

		ResultSet rset = null;
		Statement stmt = null;
		String sql = null;
		try {
			log.debug("execute select reference query");
			stmt = connection.createStatement();
			sql= dbStatements.getReferencesSql(this.context.getSchema(), table);
			
			rset = stmt
					.executeQuery(sql);

			while (rset.next()) {
				Column sourceCol = new Column();
				Schema sourceSchema = new Schema(rset.getString("table_schema"));
				Table sourceTable = new Table();
				sourceTable.setName(rset.getString("table_name"));
				sourceTable.setSchema(sourceSchema);
				sourceCol.setTable(sourceTable);
				sourceCol.setName(rset.getString("column_name"));
				sourceCol.setType(rset.getString("data_type"));
				
				Column targetCol = new Column();
				Schema targetSchema = new Schema(rset.getString("r_table_schema"));
				Table targetTable = new Table();
				targetTable.setName(rset.getString("r_table_name"));
				targetTable.setSchema(targetSchema);
				targetCol.setTable(targetTable);
				targetCol.setName(rset.getString("r_column_name"));
				targetCol.setType(rset.getString("r_data_type"));
				
				if (!this.context.isTableBlacklisted(targetTable.getName())
						&& ! this.context.isTableBlacklisted(sourceTable.getName())) {
					Reference reference = new Reference();
					reference.setSourceColumn(sourceCol);
					reference.setTargetColumn(targetCol);
				
					result.add(reference);
				} else {
					log.debug("Reference from " + sourceTable.getName() + " to " + targetTable.getName() + " blacklisted");
				}
			}

		} catch (SQLException e) {
			log.error("", e);
		} finally {
			try {
				rset.close();
			} catch (Exception e) {
			}
			try {
				stmt.close();
			} catch (Exception e) {
			}
		}

		return result;		
	}
	
	/**
	 * Returns all indexes for the given table.
	 * @param tableName
	 * @return
	 */
	public ArrayList<Index> getAllIndexes(String tableName) {
		ArrayList<Index> result = new ArrayList<Index>();
		
		Table table = new Table(
				new Schema(this.context.getSchema()), 
				tableName);
		
		ResultSet rset = null;
		Statement stmt = null;
		String sql = null;
		try {
			log.debug("execute select indexes query");
			stmt = connection.createStatement();
			sql= dbStatements.getIndexesSql(this.context.getSchema(), tableName);
			
			rset = stmt
					.executeQuery(sql);

			while (rset.next()) {
				String indexName = rset.getString("index_name");
				Index index = null;
				for (int i = 0; i < result.size(); i++) {
					if (result.get(i).getName().equals(indexName)) {
						index = result.get(i);
						break;
					}
				}
				
				if (index == null) {
					index = new Index();
					index.setName(indexName);
					index.setUniqueness(rset.getString("uniqueness"));
					result.add(index);
				}
				
				Column col = new Column();
				
				col.setTable(table);
				col.setName(rset.getString("column_name"));
				col.setType(rset.getString("data_type"));
				
				index.addColumn(col);
			}

		} catch (SQLException e) {
			log.error("", e);
		} finally {
			try {
				rset.close();
			} catch (Exception e) {
			}
			try {
				stmt.close();
			} catch (Exception e) {
			}
		}

		return result;		
	}
	
	/**
	 * Returns the primary-key columns for the given table.
	 * @param table
	 * @return
	 */
	public ArrayList<String> getPrimaryKeyColumns(String table) {
		ArrayList<String> result = new ArrayList<String>();

		ResultSet rset = null;
		Statement stmt = null;
		String sql = null;
		try {
			log.debug("execute select pk column query");
			stmt = connection.createStatement();
			sql= dbStatements.getPrimaryKeyColumns(this.context.getSchema(), table);
			
			log.debug("sql: " + sql);
			rset = stmt
					.executeQuery(sql);

			while (rset.next()) {
				result.add(rset.getString("column_name"));
			}

		} catch (SQLException e) {
			log.error("", e);
		} finally {
			try {
				rset.close();
			} catch (Exception e) {
			}
			try {
				stmt.close();
			} catch (Exception e) {
			}
		}

		return result;		
	}
	
	/**
	 * Close all resources.
	 */
	public void close() {
		try {
			connection.close();
		} catch (SQLException e) {
			log.error("", e);
		}
	}
}
